<?php
/**
 * Created by PhpStorm.
 * User: gxh
 * Date: 16-12-30
 * Time: 上午10:11
 */

namespace Inbound\Model;

use Inbound\Model\InboundshipmentplanModel;
use Inbound\Service\PrepareneedsService;

class InboundshipmentplandetailModel extends InboundshipmentplanModel
{
    public $Inboundshipmentplandetail = NULL;
    //备货需求明细表
    static $table = 'inbound_shipment_plan_detail';
    //数据库对象
    public $prepare_needs = NULL;
    //数据
    public $data = array();

    //需求明细明细数据
    public $Inboundshipmentplandetail_data = array();

    public $Inboundshipmentplandetail_id   = '';

    /**
     * InboundshipmentplandetailModel constructor.
     * @param string $table
     * @param array  $param
     */
    public function __construct($table = '',$param = array())
    {
        $this->Inboundshipmentplandetail = M(self::$table,'fba_',$this->_db);
        parent::__construct();
    }

    /*
     * 获取数据库model
     * */
    public function add_detail_by_table($_array = array(),&$model = NULL)
    {
        //插入平台计划单详情
        $this->Inboundshipmentplandetail_id = $model
            ->table($this->_db.'.fba_'.self::$table)
            ->add($_array);

        return $this->Inboundshipmentplandetail_id;
    }

    /**
     * @param array $_array
     * @return mixed
     * 过滤查询条件
     */
    public function get_detail_model(&$_array = array(),$field = null)
    {
        if($_array){
            $_array = array_filter($_array);
        }

        if(!$field){
            $field ="a.id,a.batch_code,a.create_time,b.*";
        }


        $this->Inboundshipmentplandetail->table('fba_inbound_shipment_plan a')
            ->join('fba_inbound_shipment_plan_detail b on a.id = b.inbound_shipment_plan_id')
            ->field($field);

        foreach($_array as $key => $_arr){
            switch($key){
                case 'create_time_from':
                    $_array['create_time'][] = array(
                        'gt',
                        $_arr
                    );
                    unset($_array['create_time_from']);
                    break;
                case 'create_time_to':
                    $_array['create_time'][] = array(
                        'lt',
                        $_arr
                    );
                    unset($_array['create_time_to']);
                    break;
                case 'batch_code':
                    $_array['a.batch_code'] = $_arr;
                    unset($_array['batch_code']);
                    break;
                case 'status':
                    $_array['a.status'] = $_arr;
                    unset($_array['status']);
                    break;
                case 'tranfer_hopper_id':
                    $_array['a.tranfer_hopper_id'] = $_arr;
                    unset($_array['tranfer_hopper_id']);
                    break;
                case 'name':
                unset($_array['name']);
                break;

            }
        }
        return $this->Inboundshipmentplandetail->where($_array);
    }

    /**
     *更新操作
     */
    public  function  update_detail($_array = array(),$data = array()){
        $res = $this->Inboundshipmentplandetail->where($_array)->save($data);
        //echo $this->Inboundshipmentplandetail->getLastSql();exit;
        return $res;
    }

    /**
     * 查询某个计划单下对应的明细状态
     */
    public function get_datail_data_by_id($param,$_array){
        $data = $this->Inboundshipmentplandetail
            ->where($param)
            ->field('status,inbound_shipment_plan_id')
            ->select();
        return $data;
    }

    /**
     * @param $param
     * @param $_array
     * @return mixed
     * 根据条件获取明细信息
     */
    public function queryDetails ($param, $_array) {
        $data = $this->Inboundshipmentplandetail
            ->where($param)
            ->select();
        return $data;
    }

    public function getOceanShipping ($options) {
        $sql = "SELECT p.account_id,p.account_name AS accountName,pd.seller_sku AS sku,SUM(pd.quantity) AS 'ocean',
                      `as`.`shorthand_code`,`ass`.`asin`,`ass`.`private_sku`,`ass`.`sale_status_id`,`sst`.`item`
                FROM
                  `fba_inbound_shipment_plan_detail` AS `pd`
                LEFT JOIN
                  `fba_inbound_shipment_plan` AS `p`
                ON
                  p.id = pd.`inbound_shipment_plan_id`
                LEFT JOIN
                  `api_account_seller_sku` AS `ass`
                ON
                  `ass`.`account_id` = `p`.`account_id`
                AND
                  `ass`.`seller_sku` = `pd`.`seller_sku`
                LEFT JOIN
                  `amazonorder_sites` AS `as`
                ON
                  `ass`.`site_id` = `as`.`id`
                LEFT JOIN
                  `skusystem_sku_taxrate` AS `sst`
                ON
                  `sst`.`sku` = `ass`.`private_sku`
                LEFT JOIN
                  fba_transport_plan AS tp
                ON
                  tp.id = p.`transport_plan_id`
                LEFT JOIN
                  fba_carrier_service AS s
                ON
                  s.id = tp.carrier_service_id
                WHERE
                  p.status = 60 AND s.service_name = '海运'
                AND `ass`.`private_sku` IN (" . $options['sku'] . ")
                GROUP BY
                  p.account_id,pd.seller_sku";

        $result = M('inbound_shipment_plan_detail', 'fba_', 'fbawarehouse')->query($sql);

        $oceanShipping = array();

        foreach ($result as $key => $value) {
            $oceanShipping[$value['account_id'] . ':' .$value['sku']] = $result[$key];
        }

        return !empty($oceanShipping) ? $oceanShipping : array();
    }

    public function getAirTransport ($options) {
        $sql = "SELECT p.account_id,p.account_name AS accountName,pd.seller_sku AS sku,SUM(pd.quantity) AS 'air',
                      `as`.`shorthand_code`,`ass`.`asin`,`ass`.`private_sku`,`ass`.`sale_status_id`,`sst`.`item`
                FROM
                  `fba_inbound_shipment_plan_detail` AS `pd`
                LEFT JOIN
                  `fba_inbound_shipment_plan` AS `p`
                ON
                  p.id = pd.`inbound_shipment_plan_id`
                LEFT JOIN
                  `api_account_seller_sku` AS `ass`
                ON
                  `ass`.`account_id` = `p`.`account_id`
                AND
                  `ass`.`seller_sku` = `pd`.`seller_sku`
                LEFT JOIN
                  `amazonorder_sites` AS `as`
                ON
                  `ass`.`site_id` = `as`.`id`
                LEFT JOIN
                  `skusystem_sku_taxrate` AS `sst`
                ON
                  `sst`.`sku` = `ass`.`private_sku`
                LEFT JOIN
                  fba_transport_plan AS tp
                ON
                  tp.id = p.`transport_plan_id`
                LEFT JOIN
                  fba_carrier_service AS s
                ON
                  s.id = tp.carrier_service_id
                WHERE
                  p.status = 60 AND s.service_name != '海运'
                AND `ass`.`private_sku` IN (" . $options['sku'] . ")
                GROUP BY
                  p.account_id,pd.seller_sku";

        $result = M('inbound_shipment_plan_detail', 'fba_', 'fbawarehouse')->query($sql);

        $airTransport = array();

        foreach ($result as $key => $value) {
            $airTransport[$value['account_id'] . ':' .$value['sku']] = $result[$key];
        }

        return !empty($airTransport) ? $airTransport : array();
    }

    public function getSaleConfirmQuantity ($options) {
        $sql = "SELECT a.`account_id`,a.`account_name` AS accountName,a.`private_sku`,a.`seller_sku` AS sku,
                SUM(`qty`) AS `homeNum`,`as`.`shorthand_code`,`ass`.`asin`,`ass`.`sale_status_id`,`sst`.`item`
                FROM
                (
                SELECT `account_id`,`account_name`,`sku` AS `private_sku`,`seller_sku`,`needs_quantity` AS `qty`
                FROM
                    `fba_prepare_needs_details`
                WHERE
                    `status` IN(20, 30)
                UNION ALL
                SELECT p.account_id,p.account_name,pd.sku AS `private_sku`,pd.seller_sku,pd.quantity AS `qty`
                FROM
                  `fba_inbound_shipment_plan_detail` AS `pd`
                LEFT JOIN
                  `fba_inbound_shipment_plan` AS `p`
                ON
                  p.id = pd.`inbound_shipment_plan_id`
                WHERE
                  p.status < 60 AND pd.quantity != 0
                ) a
                LEFT JOIN
                  `api_account_seller_sku` AS `ass`
                ON
                  `ass`.`account_id` = `a`.`account_id`
                AND
                  `ass`.`seller_sku` = `a`.`seller_sku`
                LEFT JOIN
                  `amazonorder_sites` AS `as`
                ON
                  `ass`.`site_id` = `as`.`id`
                LEFT JOIN
                  `skusystem_sku_taxrate` AS `sst`
                ON
                  `sst`.`sku` = `a`.`private_sku`
                WHERE
                  `a`.`private_sku` IN (" . $options['sku'] . ")
                GROUP BY
                  a.`account_id`,a.`seller_sku`";

        $result = M('inbound_shipment_plan_detail', 'fba_', 'fbawarehouse')->query($sql);


        $saleConfirmQuantity = array();

        foreach ($result as $key => $value) {
            $saleConfirmQuantity[$value['account_id'] . ':' .$value['sku']] = $result[$key];
        }

        return !empty($saleConfirmQuantity) ? $saleConfirmQuantity : array();
    }
    

}